CREATE PROCEDURE Application_FindAll
AS
BEGIN
    Select 
    ApplicationId,
    Name,
    IsPrimary
    from Application
END
GO

CREATE PROCEDURE Application_FindById
@ApplicationId int
AS
BEGIN
    Select * from Application where ApplicationId = @ApplicationId
END
GO

CREATE PROCEDURE Application_FindByName
@name nvarchar(50)
AS
BEGIN
    Select * from Application where ApplicationId = @name
END
GO

CREATE PROCEDURE Application_Insert
        @ApplicationId int ,
        @Name nvarchar (50),
        @IsPrimary bit 
AS
BEGIN
INSERT INTO Application
(
        ApplicationId,
        Name,
        IsPrimary
)
VALUES
(
        @ApplicationId,
        @Name,
        @IsPrimary
)
select @ApplicationId = @@IDENTITY
END
GO

CREATE PROCEDURE Application_Update
        @ApplicationId int ,
        @Name nvarchar (50),  
        @IsPrimary bit
AS
BEGIN
UPDATE  Application SET
    Name = @Name,
    IsPrimary = @IsPrimary
    WHERE ApplicationId = @ApplicationId
END
GO

CREATE PROCEDURE Application_Delete
    @ApplicationId int
AS
BEGIN
    DELETE FROM Application where ApplicationId = @ApplicationId
END
GO

CREATE PROCEDURE Application_Paging
    @PageSize int, 
    @PageNumber int
    AS 
    declare @RowStart int
    declare @RowEnd int
    if @PageNumber > 0 
    BEGIN	
    set @PageNumber = @PageNumber - 1
    set @RowStart = @PageSize * @PageNumber + 1;
    set @RowEnd = @RowStart  + @PageSize - 1;
    SELECT TotalRowCount = COUNT(ApplicationId) FROM Application;
    WITH MyTable as 
    (
    SELECT * ,  ROW_NUMBER() OVER (order by SortIndex) as RowNumber From  Application
    )
    SELECT * from MyTable where RowNumber >= @RowStart and RowNumber <= @RowEnd
END
GO

CREATE proc Application_SetActive
@ApplicationId int
AS
declare @Type bit
SELECT @Type =  IsActive from Application where ApplicationId = @ApplicationId
BEGIN
IF(@Type = 1)
Update Application set IsActive = 0 where ApplicationId = @ApplicationId
else
Update Application set IsActive = 1 where ApplicationId = @ApplicationId
END
GO

CREATE proc Application_SetSortIndex
@ApplicationId int, 
@Type int
AS
declare @CurrentSort int
select @CurrentSort = SortIndex from  Application where ApplicationId = @ApplicationId
BEGIN
IF(@Type = 1)
Update Application set SortIndex =  @CurrentSort + 1 where ApplicationId = @ApplicationId
else
Update Application set SortIndex =  @CurrentSort - 1 where ApplicationId = @ApplicationId
END
GO

CREATE PROCEDURE Application_RemoveSelectedItem
    @ApplicationId int
AS
BEGIN
    UPDATE Application SET IsDeleted = 1 WHERE ApplicationId = @ApplicationId
END
GO

CREATE PROCEDURE Application_RestoreSelectedItem
    @ApplicationId int
AS
BEGIN
    UPDATE Application SET IsDeleted = 0 WHERE ApplicationId = @ApplicationId
END
GO

CREATE PROCEDURE Application_RemoveAll
    @ApplicationId int
AS
BEGIN
    UPDATE Application SET IsDeleted = 1
END
GO

CREATE PROCEDURE Application_RestoreAll
    @ApplicationId int
AS
BEGIN
    UPDATE Application SET IsDeleted = 0
END
GO

